﻿/*
 ******************************************************************
 *      
 * 域名空间　	: GreatB2C.DALSQLServer
 * 类　名　称	: Producer.cs
 * 类　功　能	: 数据操作实现层
 * 类　流　程    :                             
 * 运用算法　	:                             
 * 编　写　人	: 王超             
 * 编写日期　	: 2011/4/4
 * 最近更新日期　:
 * 版　　本　	: V1.0                           
 * 描　　述　	: Descript of Class                                                 
 *                                        
 ******************************************************************
*/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

using GreatB2C.Model;
using GreatB2C.DALFactory;
using GreatB2C.DALProfile;
using GreatB2C.Config;
using GreatB2C.Utility;

namespace GreatB2C.DALSQLServer
{
    /// <summary>
    /// 类描述：
    /// </summary>
    [Serializable]
    public class Producer : DbBase , IProducer
    {
        string strSql = string.Empty;

        #region implement methods

        /// <summary>
        /// 获取获取参数参数列表
        /// </summary>
        /// <param name="ac"></param>
        /// <returns></returns>
        private SqlParameter[] GetParameters( GreatB2C.Model.Producer ac )
        {
            int i = 0;
            SqlParameter[] param = new SqlParameter[ 18 ];

            param[ i ] = new SqlParameter( "@ProducerID" , SqlDbType.Int , 4 );
            param[ i++ ].Value = ac.ProducerID;

            param[ i ] = new SqlParameter( "@ProducerType" , SqlDbType.Int , 4 );
            param[ i++ ].Value = ac.ProducerType;

            param[ i ] = new SqlParameter( "@ProducerName" , SqlDbType.NVarChar , 200 );
            param[ i++ ].Value = ac.ProducerName;

            param[ i ] = new SqlParameter( "@ProducerShortName" , SqlDbType.NVarChar , 50 );
            param[ i++ ].Value = ac.ProducerShortName;

            param[ i ] = new SqlParameter( "@ProducerPhoto" , SqlDbType.NVarChar , 255 );
            param[ i++ ].Value = ac.ProducerPhoto;

            if ( ac.BirthDay == DateTime.MinValue )
            {
                ac.BirthDay = DateTime.Now;
            }

            param[ i ] = new SqlParameter( "@BirthDay" , SqlDbType.DateTime , 8 );
            param[ i++ ].Value = ac.BirthDay;

            param[ i ] = new SqlParameter( "@Address" , SqlDbType.NVarChar , 255 );
            param[ i++ ].Value = ac.Address;

            param[ i ] = new SqlParameter( "@Postcode" , SqlDbType.NVarChar , 10 );
            param[ i++ ].Value = ac.Postcode;

            param[ i ] = new SqlParameter( "@Phone" , SqlDbType.NVarChar , 50 );
            param[ i++ ].Value = ac.Phone;

            param[ i ] = new SqlParameter( "@Fax" , SqlDbType.NVarChar , 50 );
            param[ i++ ].Value = ac.Fax;

            param[ i ] = new SqlParameter( "@Email" , SqlDbType.NVarChar , 50 );
            param[ i++ ].Value = ac.Email;

            param[ i ] = new SqlParameter( "@Homepage" , SqlDbType.NVarChar , 50 );
            param[ i++ ].Value = ac.Homepage;

            param[ i ] = new SqlParameter( "@ProducerIntro" , SqlDbType.Text );
            param[ i++ ].Value = ac.ProducerIntro;

            if ( ac.LastUseTime == DateTime.MinValue )
            {
                ac.LastUseTime = DateTime.Now;
            }

            param[ i ] = new SqlParameter( "@LastUseTime" , SqlDbType.DateTime , 8 );
            param[ i++ ].Value = ac.LastUseTime;

            param[ i ] = new SqlParameter( "@Passed" , SqlDbType.Bit , 1 );
            param[ i++ ].Value = ac.Passed;

            param[ i ] = new SqlParameter( "@onTop" , SqlDbType.Bit , 1 );
            param[ i++ ].Value = ac.onTop;

            param[ i ] = new SqlParameter( "@IsElite" , SqlDbType.Bit , 1 );
            param[ i++ ].Value = ac.IsElite;

            param[ i ] = new SqlParameter( "@Hits" , SqlDbType.Int , 4 );
            param[ i++ ].Value = ac.Hits;


            return param;
        }

        /// <summary>
        /// 检查是否有重复记录。
        /// 注意：这里要手动修改一下，需要加入次主键做为第二条件（主要是为了修改操作）
        /// </summary>
        /// <param name="objModel">对应的数据映射类</param>
        /// <returns>操作结果：true-成功 false-失败</returns>
        public bool IsExists( GreatB2C.Model.Producer objModel )
        {
            string Sql = "SELECT Count(*) "
                            + " FROM "
                            + "[dbo].[" + Prefix + "Producer]"
                            + " WHERE "
                            + "	[ProducerID] <> @ProducerID"
                            + " and ProducerName=@ProducerName"
                            ;

            try
            {
                int i=0;
                SqlParameter[] param = new SqlParameter[ 2 ];

                param[ i ] = new SqlParameter( "@ProducerID" , SqlDbType.Int , 4 );
                param[ i++ ].Value = objModel.ProducerID;

                param[ i ] = new SqlParameter( "@ProducerType" , SqlDbType.Int , 4 );
                param[ i++ ].Value = objModel.ProducerType;

                int count = Convert.ToInt32( DbHelper.ExecuteScalar( CommandType.Text , Sql , param ).ToString( ) );
                if ( count > 0 )
                {
                    return true;
                }

                return false;
            }
            catch ( Exception ex )
            {
                Console.Write( ex.Message );
                return false;
            }
        }

        /// <summary>
        /// 检索出表中所有记录	
        /// </summary>
        /// <param name="aryResult">返回对象数据集合</param>
        /// <returns>操作结果：true-成功 false-失败</returns>
        public DataTable ListProducer( )
        {
            DataTable dtData = new DataTable( );
            string Sql = "SELECT "
                            + "[ProducerID],"
                            + "[ProducerType],"
                            + "[ProducerName],"
                            + "[ProducerShortName],"
                            + "[ProducerPhoto],"
                            + "[BirthDay],"
                            + "[Address],"
                            + "[Postcode],"
                            + "[Phone],"
                            + "[Fax],"
                            + "[Email],"
                            + "[Homepage],"
                            + "[ProducerIntro],"
                            + "[LastUseTime],"
                            + "[Passed],"
                            + "[onTop],"
                            + "[IsElite],"
                            + "[Hits]"
                            + " FROM "
                            + "[dbo].[" + Prefix + "Producer]";

            try
            {
                return DbHelper.ExecuteTable( CommandType.Text , Sql , null );
            }
            catch ( Exception ex )
            {
                Console.Write( ex.Message );
                return null;
            }
        }

        /// <summary>
        /// 根据表中主键ID查询记录信息
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <returns>返回对象数据映射</returns>
        public GreatB2C.Model.Producer ListByID( GreatB2C.Model.Producer objModel )
        {
            string Sql = "SELECT * "
                            + " FROM "
                            + "[dbo].[" + Prefix + "Producer]"
                            + " WHERE "
                            + "	[ProducerID] = @ProducerID";

            try
            {
                SqlParameter[] param = GetParameters( objModel );

                using ( DataTable dtData = DbHelper.ExecuteTable( CommandType.Text , Sql , param ) )
                {
                    if ( dtData.Rows.Count > 0 )
                    {
                        GreatB2C.Model.Producer modClass = new GreatB2C.Model.Producer( );

                        modClass.ProducerID = Convert.ToInt32( dtData.Rows[ 0 ][ "ProducerID" ].ToString( ) );
                        modClass.ProducerType = Convert.ToInt32( dtData.Rows[ 0 ][ "ProducerType" ].ToString( ) );
                        modClass.ProducerName = dtData.Rows[ 0 ][ "ProducerName" ].ToString( );
                        modClass.ProducerShortName = dtData.Rows[ 0 ][ "ProducerShortName" ].ToString( );
                        modClass.ProducerPhoto = dtData.Rows[ 0 ][ "ProducerPhoto" ].ToString( );
                        modClass.BirthDay = Convert.ToDateTime( dtData.Rows[ 0 ][ "BirthDay" ].ToString( ) );
                        modClass.Address = dtData.Rows[ 0 ][ "Address" ].ToString( );
                        modClass.Postcode = dtData.Rows[ 0 ][ "Postcode" ].ToString( );
                        modClass.Phone = dtData.Rows[ 0 ][ "Phone" ].ToString( );
                        modClass.Fax = dtData.Rows[ 0 ][ "Fax" ].ToString( );
                        modClass.Email = dtData.Rows[ 0 ][ "Email" ].ToString( );
                        modClass.Homepage = dtData.Rows[ 0 ][ "Homepage" ].ToString( );
                        modClass.ProducerIntro = dtData.Rows[ 0 ][ "ProducerIntro" ].ToString( );
                        modClass.LastUseTime = Convert.ToDateTime( dtData.Rows[ 0 ][ "LastUseTime" ].ToString( ) );
                        modClass.Passed = Convert.ToBoolean( dtData.Rows[ 0 ][ "Passed" ].ToString( ) );
                        modClass.onTop = Convert.ToBoolean( dtData.Rows[ 0 ][ "onTop" ].ToString( ) );
                        modClass.IsElite = Convert.ToBoolean( dtData.Rows[ 0 ][ "IsElite" ].ToString( ) );
                        modClass.Hits = Convert.ToInt32( dtData.Rows[ 0 ][ "Hits" ].ToString( ) );

                        return modClass;
                    }
                }

                return null;
            }
            catch ( Exception ex )
            {
                Console.Write( ex.Message );
                return null;
            }
        }

        /// <summary>
        /// 新增一条新记录到表中
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <returns>操作结果：true-成功 false-失败</returns>
        public bool Insert( GreatB2C.Model.Producer objModel )
        {
            string Sql = "INSERT INTO "
                        + "[dbo].[" + Prefix + "Producer]"
                        + "("
                        + "[ProducerType],"
                        + "[ProducerName],"
                        + "[ProducerShortName],"
                        + "[ProducerPhoto],"
                        + "[BirthDay],"
                        + "[Address],"
                        + "[Postcode],"
                        + "[Phone],"
                        + "[Fax],"
                        + "[Email],"
                        + "[Homepage],"
                        + "[ProducerIntro],"
                        + "[LastUseTime],"
                        + "[Passed],"
                        + "[onTop],"
                        + "[IsElite],"
                            + "[Hits]"
                        + ") VALUES ("
                        + "@ProducerType,"
                        + "@ProducerName,"
                        + "@ProducerShortName,"
                        + "@ProducerPhoto,"
                        + "@BirthDay,"
                        + "@Address,"
                        + "@Postcode,"
                        + "@Phone,"
                        + "@Fax,"
                        + "@Email,"
                        + "@Homepage,"
                        + "@ProducerIntro,"
                        + "@LastUseTime,"
                        + "@Passed,"
                        + "@onTop,"
                        + "@IsElite,"
                            + "@Hits"
                        + ")";

            try
            {
                SqlParameter[] param = GetParameters( objModel );

                int affect = DbHelper.ExecuteNonQuery( CommandType.Text , Sql , param );

                if ( affect == 1 )
                    return true;
                else
                    return false;
            }
            catch ( Exception ex )
            {
                Console.Write( ex.Message );
                return false;
            }
        }

        /// <summary>
        /// 根据主键ID更新指定记录
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <returns>操作结果：true-成功 false-失败</returns>
        public bool Update( GreatB2C.Model.Producer objModel )
        {
            string Sql = "UPDATE "
                        + "[dbo].[" + Prefix + "Producer]"
                        + " SET "
                        + "[ProducerType] = @ProducerType,"
                        + "[ProducerName] = @ProducerName,"
                        + "[ProducerShortName] = @ProducerShortName,"
                        + "[ProducerPhoto] = @ProducerPhoto,"
                        + "[BirthDay] = @BirthDay,"
                        + "[Address] = @Address,"
                        + "[Postcode] = @Postcode,"
                        + "[Phone] = @Phone,"
                        + "[Fax] = @Fax,"
                        + "[Email] = @Email,"
                        + "[Homepage] = @Homepage,"
                        + "[ProducerIntro] = @ProducerIntro,"
                        + "[LastUseTime] = @LastUseTime"
                        //+ "[Passed] = @Passed,"
                        //+ "[onTop] = @onTop,"
                        //+ "[IsElite] = @IsElite,"
                            //+ "[Hits] = @Hits"
                        + " WHERE "
                        + "[ProducerID] = @ProducerID"
                        ;

            try
            {
                SqlParameter[] param = GetParameters( objModel );

                int affect = DbHelper.ExecuteNonQuery( CommandType.Text , Sql , param );

                if ( affect == 1 )
                    return true;
                else
                    return false;
            }
            catch ( Exception ex )
            {
                Console.Write( ex.Message );
                return false;
            }
        }

        /// <summary>
        /// 根据主键删除指定记录
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <returns>操作结果：true-成功 false-失败</returns>
        public bool Delete( GreatB2C.Model.Producer objModel )
        {
            string Sql = "DELETE FROM "
                            + "[dbo].[" + Prefix + "Producer]"
                            + " WHERE "
                            + "[ProducerID] = @ProducerID"
                            ;

            try
            {
                SqlParameter[] param = GetParameters( objModel );

                int affect = DbHelper.ExecuteNonQuery( CommandType.Text , Sql , param );

                if ( affect == 1 )
                    return true;
                else
                    return false;
            }
            catch ( Exception ex )
            {
                Console.Write( ex.Message );
                return false;
            }
        }

        /// <summary>
        /// 根据ID列表批量删除记录
        /// 数据格式：1,2,3,4,5
        /// </summary>
        /// <param name="strList">ID列表</param>
        /// <returns>操作结果：true-成功 false-失败</returns>
        public bool DeleteList( string strList )
        {
            string Sql = "DELETE FROM "
                            + "[dbo].[" + Prefix + "Producer]"
                            + " WHERE "
                            + "[ProducerID] in ('" + strList + "')"
                            ;

            try
            {
                int affect = DbHelper.ExecuteNonQuery( CommandType.Text , Sql , null );

                if ( affect == 1 )
                    return true;
                else
                    return false;
            }
            catch ( Exception ex )
            {
                Console.Write( ex.Message );
                return false;
            }
        }

        /// <summary>
        /// 根据主键ID更新启用标识
        /// </summary>
        /// <param name="producerID">厂商编号</param>
        /// <param name="passed">是否启用</param>
        /// <returns>操作结果：true-成功 false-失败</returns>
        public bool UpdateProducerPassed( int producerID , bool passed )
        {
            string Sql = "UPDATE "
                        + "[dbo].[" + Prefix + "Producer]"
                        + " SET "
                        + "[Passed] = @Passed"
                        + " WHERE "
                        + "[ProducerID] = @ProducerID"
                        ;

            try
            {
                int i = 0;
                SqlParameter[] param = new SqlParameter[ 2 ];

                param[ i ] = new SqlParameter( "@ProducerID" , SqlDbType.Int , 4 );
                param[ i++ ].Value = producerID;

                param[ i ] = new SqlParameter( "@Passed" , SqlDbType.Bit , 1 );
                param[ i++ ].Value = passed;

                int affect = DbHelper.ExecuteNonQuery( CommandType.Text , Sql , param );

                if ( affect == 1 )
                    return true;
                else
                    return false;
            }
            catch ( Exception ex )
            {
                Console.Write( ex.Message );
                return false;
            }
        }

        /// <summary>
        /// 根据主键ID更新固顶标识
        /// </summary>
        /// <param name="producerID">厂商编号</param>
        /// <param name="passed">是否固顶</param>
        /// <returns>操作结果：true-成功 false-失败</returns>
        public bool UpdateProducerTop( int producerID , bool onTop )
        {
            string Sql = "UPDATE "
                        + "[dbo].[" + Prefix + "Producer]"
                        + " SET "
                        + "[onTop] = @onTop"
                        + " WHERE "
                        + "[ProducerID] = @ProducerID"
                        ;

            try
            {
                int i = 0;
                SqlParameter[] param = new SqlParameter[ 2 ];

                param[ i ] = new SqlParameter( "@ProducerID" , SqlDbType.Int , 4 );
                param[ i++ ].Value = producerID;

                param[ i ] = new SqlParameter( "@onTop" , SqlDbType.Bit , 1 );
                param[ i++ ].Value = onTop;

                int affect = DbHelper.ExecuteNonQuery( CommandType.Text , Sql , param );

                if ( affect == 1 )
                    return true;
                else
                    return false;
            }
            catch ( Exception ex )
            {
                Console.Write( ex.Message );
                return false;
            }
        }

        /// <summary>
        /// 根据主键ID更新推荐状态
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <returns>操作结果：true-成功 false-失败</returns>
        public bool UpdateProducerElite( int producerID , bool isElite )
        {
            string Sql = "UPDATE "
                        + "[dbo].[" + Prefix + "Producer]"
                        + " SET "
                        + "[IsElite] = @IsElite"
                        + " WHERE "
                        + "[ProducerID] = @ProducerID"
                        ;

            try
            {
                int i = 0;
                SqlParameter[] param = new SqlParameter[ 2 ];

                param[ i ] = new SqlParameter( "@ProducerID" , SqlDbType.Int , 4 );
                param[ i++ ].Value = producerID;

                param[ i ] = new SqlParameter( "@IsElite" , SqlDbType.Bit , 1 );
                param[ i++ ].Value = isElite;

                int affect = DbHelper.ExecuteNonQuery( CommandType.Text , Sql , param );

                if ( affect == 1 )
                    return true;
                else
                    return false;
            }
            catch ( Exception ex )
            {
                Console.Write( ex.Message );
                return false;
            }
        }

        /// <summary>
        /// 根据厂商类别检索出表中所有记录	
        /// </summary>
        /// <param name="producerType">厂商类别</param>
        /// <returns>操作结果：true-成功 false-失败</returns>
        public DataTable ListProducerByType( int producerType )
        {
            DataTable dtData = new DataTable( );
            string Sql = "SELECT * FROM "
                            + "[dbo].[" + Prefix + "Producer] where ProducerType = @ProducerType";

            try
            {
                SqlParameter param = new SqlParameter( "@ProducerType" , SqlDbType.Int , 4 );
                param.Value = producerType;

                return DbHelper.ExecuteTable( CommandType.Text , Sql , param );
            }
            catch ( Exception ex )
            {
                Console.Write( ex.Message );
                return null;
            }
        }

        /// <summary>
        /// 根据厂商启用状态检索出表中厂商信息	
        /// </summary>
        /// <param name="passed">启用状态</param>
        /// <returns>数据集合</returns>
        public DataTable ListProducerByPassed( bool passed )
        {
            DataTable dtData = new DataTable( );
            string Sql = "SELECT * FROM "
                            + "[dbo].[" + Prefix + "Producer] where Passed = @Passed";

            try
            {
                SqlParameter param = new SqlParameter( "@Passed" , SqlDbType.Bit );
                param.Value = passed;

                return DbHelper.ExecuteTable( CommandType.Text , Sql , param );
            }
            catch ( Exception ex )
            {
                Console.Write( ex.Message );
                return null;
            }
        }

        #endregion
    }
}
